package com.brainsitter.db;

import java.sql.Time;
import java.util.ArrayList;
import java.sql.Date;

import com.brainsitter.Reward;
import com.brainsitter.db.tables.Note;
import com.brainsitter.db.tables.Profile;
import com.brainsitter.db.tables.Task;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {
	
	private static final String DB_NAME = "brainsitter90";
	private static final int DB_VERSION = 1;
	private static final String CREATE_TABLE_TASK =
			"CREATE TABLE task(taskId integer PRIMARY KEY, taskName text, description text, dueTime text, dueDate text, status text)";
	private static final String CREATE_TABLE_NOTE =
			"CREATE TABLE note(noteId integer PRIMARY KEY, noteName text, dateCreated text, timeCreated text, content text)";
	private static final String CREATE_TABLE_PROFILE =
			"CREATE TABLE profile(name text PRIMARY KEY, points integer, twitterName text, twitterPassword text)";

	public DBHandler(Context context, String name, CursorFactory factory,
			int version) {
		super(context, DB_NAME, null, DB_VERSION);
		// TODO Auto-generated constructor stub
	}
	
	public DBHandler(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE_TASK); //Create the table for task
		db.execSQL(CREATE_TABLE_NOTE);
		db.execSQL(CREATE_TABLE_PROFILE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public void addTask(Task task) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("taskName", task.getTaskName());
		cv.put("description", task.getTaskDescription());
		cv.put("dueTime", String.valueOf(task.getDueTime()));
		cv.put("dueDate", String.valueOf(task.getDueDate()));
		
		Log.d(null, task.getTaskId() + "");
		
		db.insert("task", null, cv);
		
		db.close();
	}
	
	

	public void addProfile(Profile x) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("name", x.getName());
		cv.put("points", x.getPoints());
		cv.put("twitterName", x.getTwitterName());
		cv.put("twitterPassword", x.getTwitterPassword());
		
		
		db.insert("profile", null, cv);
		
		db.close();
	}
	
	
	
	public void addNote(Note note) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("noteName", note.getNoteName());
		cv.put("dateCreated", String.valueOf(note.getDateCreated()));
		cv.put("timeCreated", String.valueOf(note.getTimeCreated()));
		cv.put("content", note.getContent());
		
		db.insert("note", null, cv);
		
		db.close();
	}
	
	public int getTaskCount()
	{
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor cur = db.rawQuery("SELECT * FROM task", null);
		
		int x = cur.getCount();
		
		cur.close();
		db.close();
		
		return x;
	}
	
	public int getCompletedTaskCount()
	{
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor cur = db.rawQuery("SELECT * FROM task WHERE status = '"+ Task.FINISH +"'", null);
		
		int x = cur.getCount();
		
		cur.close();
		db.close();
		
		return x;
	}
	
	public int getFailedTaskCount()
	{
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor cur = db.rawQuery("SELECT * FROM task WHERE status = '"+ Task.NOT_FINISH +"'", null);
		
		int x = cur.getCount();
		
		cur.close();
		db.close();
		
		return x;
	}
	
	public int getNoteCount()
	{
		SQLiteDatabase db = this.getWritableDatabase();
		
		Cursor cur = db.rawQuery("SELECT * FROM note", null);
		
		int x = cur.getCount();
		
		cur.close();
		db.close();
		
		return x;
	}
	
	public void updateTask(Task task){
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put("taskId", task.getTaskId());
		values.put("taskName", task.getTaskName());
		values.put("description", task.getTaskDescription());
		values.put("dueTime", String.valueOf(task.getDueTime()));
		values.put("dueDate", String.valueOf(task.getDueDate()));
		
		db.update("task", values, "taskId = ?",
				new String[] { String.valueOf(task.getTaskId()) });
		db.close();
	}
	
	public void updatePoints(int points){
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put("points", points);

		
		db.update("profile", values, null, null);
		db.close();
	}
	
	public void updateNote(Note note){
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put("noteId", note.getNoteId());
		values.put("noteName", note.getNoteName());
		values.put("dateCreated", String.valueOf(note.getDateCreated()));
		values.put("timeCreated", String.valueOf(note.getTimeCreated()));
		values.put("content", note.getContent());
		
		db.update("note", values, "noteId = ?",
				new String[] { String.valueOf(note.getNoteId()) });
		db.close();
	}
	
	public void deleteTask(Task task){
		SQLiteDatabase db = this.getWritableDatabase();
		
		db.delete("task", "taskId = ?",
				new String[] { String.valueOf(task.getTaskId()) });
		db.close();
	}
	
	public void deleteNote(Note note){
		SQLiteDatabase db = this.getWritableDatabase();
		
		db.delete("note", "noteId = ?",
				new String[] { String.valueOf(note.getNoteId()) });
		db.close();
	}
	
	public ArrayList<Task> getAllTasks() {
		ArrayList<Task> taskList = new ArrayList<Task>();
		String selectQuery = "SELECT * FROM task ";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if(cursor.moveToFirst()){
			do{
				Task task = new Task();
				task.setTaskId(cursor.getInt(0));
				task.setTaskName(cursor.getString(1));
				task.setTaskDescription(cursor.getString(2));
				task.setDueTime(Time.valueOf(cursor.getString(3)));
				task.setDueDate(Date.valueOf(cursor.getString(4)));
				
				taskList.add(task);

			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return taskList;
	}
	
	public ArrayList<Note> getAllNotes(){
		ArrayList<Note> noteList = new ArrayList<Note>();
		String selectQuery = "SELECT * FROM note ORDER BY noteId desc";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if(cursor.moveToFirst()){
			do{
				Note note = new Note();
				note.setNoteId(cursor.getInt(0));
				note.setNoteName(cursor.getString(1));
				note.setDateCreated(Date.valueOf(cursor.getString(2)));
				note.setTimeCreated(Time.valueOf(cursor.getString(3)));
				note.setContent(cursor.getString(4));
				
				noteList.add(note);

			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return noteList;
	}

	public ArrayList<Task> getAllCompletedTasks(){
		ArrayList<Task> taskList = new ArrayList<Task>();
		String selectQuery = "SELECT * FROM task WHERE status='"+ Task.FINISH +"' ORDER BY taskId desc";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if(cursor.moveToFirst()){
			do{
				Task task = new Task();
				task.setTaskId(cursor.getInt(1));
				task.setTaskName(cursor.getString(2));
				task.setTaskDescription(cursor.getString(3));
				task.setDueTime(Time.valueOf(cursor.getString(4)));
				task.setDueDate(Date.valueOf(cursor.getString(5)));
				
				taskList.add(task);

			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return taskList;
	}
	
	public ArrayList<Task> getAllFailedTasks(){
		ArrayList<Task> taskList = new ArrayList<Task>();
		String selectQuery = "SELECT * FROM task WHERE status='"+ Task.NOT_FINISH +"' ORDER BY taskId desc";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if(cursor.moveToFirst()){
			do{
				Task task = new Task();
				task.setTaskId(cursor.getInt(1));
				task.setTaskName(cursor.getString(2));
				task.setTaskDescription(cursor.getString(3));
				task.setDueTime(Time.valueOf(cursor.getString(4)));
				task.setDueDate(Date.valueOf(cursor.getString(5)));
				
				taskList.add(task);

			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return taskList;
	}
	
	public int getPoints() {
		String selectQuery = "SELECT * FROM profile";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		int points = 0;
		if(cursor.moveToFirst()) {
		points = cursor.getInt(0);
		}
		db.close();
		return points;
	}
	
	public Profile getProfile() {
		String selectQuery = "SELECT * FROM profile";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		Profile prof = null;
		
		if(cursor.moveToFirst()){
			do{
				prof = new Profile();
				prof.setName(cursor.getString(0));
				prof.setPoints(cursor.getInt(1));
			}while(cursor.moveToNext());		
		}
		cursor.close();
		db.close();
			
		return prof;
	}
	
	
	
	public void updateProfile (Profile prof) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put("name", prof.getName());
		values.put("points", prof.getPoints());
		db.update("note", values, null, null);
		db.close();
	}
}